package TeachLesson.CourseWork;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

//课程作业业务逻辑类
public class CourseWorkDao {
    public Connection getCon(){
        Connection con=null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String url="jdbc:mysql://localhost:3306/jwglxt?serverTimezone=UTC&characterEncoding=utf-8";
            con= DriverManager.getConnection(url,"root","root");
        }catch (Exception e){
            e.printStackTrace();
        }
        return con;
    }


    //新增作业
    public int addCourseWork(String workerNo,String courseNo,String endTime,String title,String workDesc,String evaluteType,String filePath){
        Connection con=this.getCon();
        try {
            //sql语句
            String sql="insert into tb_courseWork (workerNo,courseNo,endTime,title,workDesc,evaluteType,filePath) values(?,?,?,?,?,?,?)";
            PreparedStatement ps=con.prepareStatement(sql);
            ps.setString(1,workerNo);
            ps.setString(2,courseNo);
            ps.setString(3,endTime);
            ps.setString(4,title);
            ps.setString(5,workDesc);
            ps.setString(6,evaluteType);
            ps.setString(7,filePath);
            //执行sql
            int row=ps.executeUpdate();
            ps.close();
            con.close();
            return row;
        }catch (Exception e){
            e.printStackTrace();
            return 0;
        }

    }

    //依据工号与课程号查询课程
    public ArrayList<CourseWorkEnity> getCourseWork(String workerNo,String courseNo){
        //获取数据库连接
        Connection con=this.getCon();
        try {
            //sql
            String sql="select id,courseNo,workerNo,endTime,evaluteType,title,workDesc,filePath from tb_coursework where courseNo=? and workerNo=?";
            //创建对象
            PreparedStatement ps=con.prepareStatement(sql);
            ps.setString(1,courseNo);
            ps.setString(2,workerNo);
            //执行
            ResultSet rs =ps.executeQuery();
            ArrayList<CourseWorkEnity> list=new ArrayList<>();
            while (rs.next()){
                CourseWorkEnity courseWorkEnity=new CourseWorkEnity();
                courseWorkEnity.setId(rs.getInt("id"));
                courseWorkEnity.setCourseNo(rs.getString("courseNo"));
                courseWorkEnity.setWorkerNo(rs.getString("workerNo"));
                courseWorkEnity.setEndTime(rs.getString("endTime"));
                courseWorkEnity.setEvaluteType(rs.getString("evaluteType"));
                courseWorkEnity.setTitle(rs.getString("title"));
                courseWorkEnity.setWorkDesc(rs.getString("workDesc"));
                courseWorkEnity.setFilePath(rs.getString("filePath"));
                list.add(courseWorkEnity);
            }
            rs.close();
            ps.close();
            con.close();
            return list;
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }
    }
    //依据ID查询
    //依据工号与课程号查询课程
    public CourseWorkEnity getCourseWork(int id){
        //获取数据库连接
        Connection con=this.getCon();
        try {
            //sql
            String sql="select id,courseNo,workerNo,endTime,evaluteType,title,workDesc,filePath from tb_coursework where id=?";
            //创建对象
            PreparedStatement ps=con.prepareStatement(sql);
            ps.setInt(1,id);
            //执行
            ResultSet rs =ps.executeQuery();
            CourseWorkEnity courseWorkEnity=new CourseWorkEnity();
            while (rs.next()){
                courseWorkEnity.setId(rs.getInt("id"));
                courseWorkEnity.setCourseNo(rs.getString("courseNo"));
                courseWorkEnity.setWorkerNo(rs.getString("workerNo"));
                courseWorkEnity.setEndTime(rs.getString("endTime"));
                courseWorkEnity.setEvaluteType(rs.getString("evaluteType"));
                courseWorkEnity.setTitle(rs.getString("title"));
                courseWorkEnity.setWorkDesc(rs.getString("workDesc"));
                courseWorkEnity.setFilePath(rs.getString("filePath"));
            }
            rs.close();
            ps.close();
            con.close();
            return courseWorkEnity;
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }
    }
    //依据ID删除
    public int deleteCourseWork(int id){
        Connection con=this.getCon();
        try {
            //SQL
            String sql="delete from tb_coursework where id=?";
            //创建PrepareStatment
            PreparedStatement ps=con.prepareStatement(sql);
            //执行
            ps.setInt(1,id);
            int row=ps.executeUpdate();
            ps.close();
            con.close();
            return row;
        }catch (Exception e){
            e.printStackTrace();
            return 0;
        }
    }


}
